if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInfoEd_RefProtSummaryPopulate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInfoEd_RefProtSummaryPopulate]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[spInfoEd_RefProtSummaryPopulate] AS


SET NOCOUNT ON

UPDATE dbo.tempProtocolRefSummary
SET nProtocolID =  AP.nAnimalProtID,
sProtocolType = 'IACUC',
sProtocolTitle = CAST(AP.sAnimalTitle AS VARCHAR(250)),
sOverallStatus = CASE WHEN charindex(' ', AP.sOverallStatus) > 0 THEN left(AP.sOverallStatus, charindex(' ', AP.sOverallStatus) - 1) 
	ELSE AP.sOverallStatus
	END,
sDateApproved = AP.dtAnimalApproval, 
sDateExpired = AP.dtAnimalExpiration,
sReview = CASE WHEN AP.dtAnimalApproval IS NULL THEN NULL
	ELSE BR.sDocumentType
	END,
sSponsorName = ISNULL(S.sName,''), 
sPIUserName = ISNULL(r.sUsername, ''),
sPILastName = r.sLastName, 
sPIFirstName = r.sFirstName
FROM tblAnimalProtocol AP JOIN tempProtocolRefSummary Ref 
	ON Ref.sFolderNumber = AP.sFolderNumber
LEFT JOIN tblAnimalBoardReview BR
	ON AP.nAnimalProtID = BR.nAnimalProtID
	AND BR.nMailGroupID = 8
	AND BR.sBoardStatus in ('Requires Modification','Approved')
	AND BR.sDocumentType IN ('Initial Review', 'Annual Review', 'Triennial Review')
	AND nBoardReviewID = (Select max(B1.nBoardReviewID) 
		from	tblAnimalBoardReview B1
		where	BR.nAnimalProtID = B1.nAnimalProtID
		AND BR.nMailGroupID = B1.nMailGroupID
		AND	B1.sDocumentType IN ('Initial Review', 'Triennial Review', 'Annual Review')
		AND	B1.sBoardStatus IN ('Requires Modification','Approved'))
JOIN	tblRecomAbstract r
	ON r.nRecomAbstractID = AP.nPIID
LEFT JOIN  tblSponsor S ON AP.nSponsorID = S.nSponsorID
WHERE AP.bCurrentStatus = 1


UPDATE dbo.tempProtocolRefSummary
SET nProtocolID =  p.nIRBProtocolID,
sProtocolType = 'IRB',
sProtocolTitle = CAST(p.sProtocolTitle AS VARCHAR(250)),
sOverallStatus = CASE WHEN p.sOverallStatusIRB like 'Not Human%' THEN 'Not Human Research'
		WHEN charindex(' ', p.sOverallStatusIRB) > 0 THEN left(p.sOverallStatusIRB, charindex(' ', p.sOverallStatusIRB) - 1)
		ELSE sOverallStatusIRB
	END, 
sDateApproved = p.dtApproval, 
sDateExpired = p.dtExpiration,
sSponsorName = ISNULL(S.sName,''), 
sPIUserName = ISNULL(r.sUsername, ''),
sPILastName = r.sLastName, 
sPIFirstName = r.sFirstName
FROM tblIRBProtocol p JOIN tempProtocolRefSummary Ref 
	ON Ref.sFolderNumber = P.sFolderNumber
	JOIN	tblRecomAbstract r
	ON r.nRecomAbstractID = p.nPIID
	LEFT JOIN	tblSponsor s
		  ON p.nSponsorID = s.nSponsorID
WHERE p.nDocumentNumber = (select max(p1.nDocumentNumber)
			FROM tblIRBProtocol p1
			WHERE p.sFolderNumber = p1.sFolderNumber)

UPDATE tempProtocolRefSummary	
SET sReview = 'Continuing Review'
FROM tblirbstatus s JOIN tempProtocolRefSummary
	ON s.nIRBProtocolID = tempProtocolRefSummary.nProtocolID
WHERE  tempProtocolRefSummary.sProtocolType = 'IRB'
	and s.sTabType	= 'Continuing Review'

AND EXISTS (			select 1
		FROM	tblirbstatus s1
		inner join	tblIRBBoardStatus b
			on	b.nIRBProtocolID  	= 	s1.nIRBProtocolID
			and b.nStatusID		=	s1.nStatusID
		inner join	tblIRBBoardStatusDetails d
			on	d.nIRBProtocolID  	= 	b.nIRBProtocolID
			and d.nStatusID		=	b.nStatusID
			and d.nMailGroupId = b.nMailGroupId
			and	 d.sStatus in ('Req. Mod.', 'Approved')
		inner join	tblListMailGroups g
			on	g.nMailGroupID		=	b.nMailGroupID
			and g.sDescription like 'IRB%'
		where s1.nStatusID = s.nStatusID)


UPDATE tempProtocolRefSummary	
SET sReview = st.sTabType
FROM tblirbstatus st JOIN tempProtocolRefSummary
	ON st.nIRBProtocolID = tempProtocolRefSummary.nProtocolID
		   AND st.sTabType ='Application'
	 JOIN	tblIRBBoardStatus b
		  ON b.nStatusID = st.nStatusID AND
		   b.nIRBProtocolID = st.nIRBProtocolID 
	JOIN	tblListMailGroups m
		ON	b.nMailGroupID = m.nMailGroupID
			AND m.sDescription LIKE 'IRB%'
	 JOIN	tblIRBBoardStatusDetails d
		  ON b.nStatusID = d.nStatusID AND
		   b.nIRBProtocolID = d.nIRBProtocolID AND
		   b.nMailGroupID = d.nMailGroupID
		   AND d.sStatus ='Approved'
WHERE  tempProtocolRefSummary.sProtocolType = 'IRB'
	AND tempProtocolRefSummary.sReview IS NULL

UPDATE tempProtocolRefSummary	
SET sReview = st.sTabType
FROM tblirbstatus st JOIN tempProtocolRefSummary
	ON st.nIRBProtocolID = tempProtocolRefSummary.nProtocolID
		   AND st.sTabType ='Application'
	JOIN	tblIRBBoardStatus b
		  ON b.nStatusID = st.nStatusID AND
		   b.nIRBProtocolID = st.nIRBProtocolID AND
		   b.sReviewType = 'Full'
	JOIN	tblListMailGroups m
		ON	b.nMailGroupID = m.nMailGroupID
			AND m.sDescription LIKE 'IRB%'
	JOIN	tblIRBBoardStatusDetails d
		  ON b.nStatusID = d.nStatusID AND
		   b.nIRBProtocolID = d.nIRBProtocolID AND
		   b.nMailGroupID = d.nMailGroupID
		   AND d.nDetailId = 
			(
			select max(d1.nDetailId)
			from tblIRBBoardStatusDetails d1
			where d1.nIRBProtocolId = d.nIRBProtocolId
			and d1.nStatusId = d.nStatusId
			and d1.nMailGroupId = d.nMailGroupId
			and d1.sStatus ='Req. Mod.')	
WHERE  tempProtocolRefSummary.sProtocolType = 'IRB'
	AND tempProtocolRefSummary.sReview IS NULL

UPDATE tempProtocolRefSummary	
SET sReview = 'Application'
WHERE  tempProtocolRefSummary.sProtocolType = 'IRB'
	AND tempProtocolRefSummary.sReview IS NULL

UPDATE tempProtocolRefSummary	
SET sReview = 'Initial Review'
WHERE  tempProtocolRefSummary.sProtocolType = 'IACUC'
	AND tempProtocolRefSummary.sReview IS NULL

DELETE tempProtocolRefSummary 
WHERE sOverallStatus IS NULL

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

